﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class ReworkPartRepository : BaseRepository<BaseEntity>, IReworkPartRepository
    {
        public ReworkPartRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        /// <summary>
        /// 获取返工返修物料拉动的数据
        /// </summary>
        /// <returns></returns>
        public List<MesWmsReworkPart> GetReworkPartInfo()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                //SqlStringBuilder.Append("select * from MES_WMS_REWORK_PART where syn_time in (select top 1 syn_time from MES_WMS_REWORK_PART where syn_flg = '10' order by syn_time) and syn_flg = '10' ");
                SqlStringBuilder.Append("select * from MES_WMS_REWORK_PART where serial_number in (select top 1 serial_number from MES_WMS_REWORK_PART where syn_flg = '10' order by syn_time) and syn_flg = '10' ");
                return dbConn.Query<MesWmsReworkPart>(SqlStringBuilder.ToString()).ToList();
            }
        }

        /// <summary>
        /// 获取订单编号
        /// </summary>
        /// <returns></returns>
        public string GetOrderNo(string stockOrder, string p)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                DynamicParameters list = new DynamicParameters();
                list.Add("@order_type", stockOrder);
                list.Add("@order_prefix", p);
                list.Add("@order_no", null, DbType.String, ParameterDirection.Output, 50);

                var hashtable = dbConn.Execute("sys_create_orderno", list, commandType: CommandType.StoredProcedure);
                string result = list.Get<string>("@order_no");
                return result;
            }
        }

        /// <summary>
        /// 处理返工返修物料拉动的数据
        /// </summary>
        /// <param name="sapSu"></param>
        /// <returns></returns>
        public bool InsertReworkPartInfo(List<MesWmsReworkPart> mesWmsReworkList)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                WmsSubcontractData wmsSubcontract = new WmsSubcontractData();
                List<string> sqlList = new List<string>();
                List<DynamicParameters> parametersList = new List<DynamicParameters>();

                StringBuilder SqlStringBuilder = new StringBuilder(1024);
                DynamicParameters parameters = new DynamicParameters();

                //List<MesWmsReworkPart> WmsRework = mesWmsReworkList.GroupBy(a => a.SerialNumber).Select(b => new MesWmsReworkPart
                //{
                //    SerialNumber = b.Key,
                //    ItemQty = b.Count()
                //}).ToList();


                //sqlList = new List<string>();
                //parametersList = new List<DynamicParameters>();
                //List<MesWmsReworkPart> mesWmsReworks = mesWmsReworkList.Where(a => a.SerialNumber == WmsRework[0].SerialNumber).ToList();
                List<MesWmsReworkPart> mesWmsReworks = mesWmsReworkList;

                StringBuilder factoryStringBuilder = new StringBuilder(1024);
                factoryStringBuilder.Append("select * from sys_factory (nolock) where factory_code='" + mesWmsReworks[0].FactoryCode + "'");//工厂代码获取工厂ID
                List<SysFactory> sysFactory = dbConn.Query<SysFactory>(factoryStringBuilder.ToString()).ToList();

                SqlStringBuilder = new StringBuilder();
                SqlStringBuilder.Append(" SELECT * from sys_pdline (nolock) WHERE pdline_code='" + mesWmsReworks[0].PdlineCode + "'");
                List<SysPdline> sysPdlines = dbConn.Query<SysPdline>(SqlStringBuilder.ToString()).ToList();

                SqlStringBuilder = new StringBuilder(1024);
                SqlStringBuilder.Append("INSERT INTO dbo.wms_move_header ");
                SqlStringBuilder.Append("           (order_no,order_type,order_status,ref_order_no,factory_id,factory_code ");
                SqlStringBuilder.Append("           ,enabled,create_userid,create_time,guid) ");
                SqlStringBuilder.Append("     VALUES(@orderNo,@orderType,@orderStatus,@refOrderNo ");
                SqlStringBuilder.Append("           ,@factoryId,@factoryCode,@enabled,@createUserid ");
                SqlStringBuilder.Append("           ,@createTime,@guid) ");
                sqlList.Add(SqlStringBuilder.ToString());

                string orderNo = GetOrderNo("ShiftProduction", "P");
                parameters = new DynamicParameters();
                parameters.Add("@orderNo", orderNo);//生产领料单
                parameters.Add("@orderType", (int)WmsEnumUtil.MoveOrderType.PICK);//生产领料单
                parameters.Add("@orderStatus", (int)WmsEnumUtil.MoveOrderStatus.CREATE);//已创建
                parameters.Add("@refOrderNo", mesWmsReworks[0].SerialNumber);//

                if (sysFactory.Count > 0)
                {
                    parameters.Add("@factoryId", sysFactory[0].FactoryId);//
                }
                else
                {
                    parameters.Add("@factoryId", 0);//
                }
                parameters.Add("@factoryCode", mesWmsReworks[0].FactoryCode);//
                parameters.Add("@enabled", "Y");//
                parameters.Add("@createUserid", 0);//
                parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                parameters.Add("@guid", Guid.NewGuid().ToString());//
                parametersList.Add(parameters);

                for (int i = 0; i < mesWmsReworks.Count; i++)
                {
                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("INSERT INTO dbo.wms_move_detail ");
                    SqlStringBuilder.Append("           (order_no,item_no,part_id,part_no,part_spec,qty,pick_qty,out_qty,rec_qty ");
                    SqlStringBuilder.Append("           ,unit,plan_date,plan_time,item_status,src_warehouse_id,src_warehouse_name,dest_warehouse_id ");
                    SqlStringBuilder.Append("           ,dest_warehouse_name,src_zone_id,src_zone_name,dest_zone_id,dest_zone_name,src_erp_warehouse,dest_erp_warehouse,factory_id ");
                    SqlStringBuilder.Append("           ,factory_code,enabled,create_userid,create_time,guid) ");
                    SqlStringBuilder.Append("     VALUES(@orderNo,@itemNo,@partId,@partNo,@partSpec,@qty,@pickQty,@outQty,@recQty,@unit,@planDate ");
                    SqlStringBuilder.Append("           ,@planTime,@itemStatus,@srcWarehouseId,@srcWarehouseName,@destWarehouseId,@destWarehouseName ");
                    SqlStringBuilder.Append("		   ,@srcZoneId,@srcZoneName,@destZoneId,@destZoneName,@srcErpWarehouse,@destErpWarehouse ");
                    SqlStringBuilder.Append("           ,@factoryId,@factoryCode,@enabled,@createUserid,@createTime,@guid) ");
                    sqlList.Add(SqlStringBuilder.ToString());
                    parameters = new DynamicParameters();
                    parameters.Add("@orderNo", orderNo);//生产领料单
                    parameters.Add("@itemNo", (i+1)*10);//序号

                    StringBuilder partNoStringBuilder = new StringBuilder(1024);
                    partNoStringBuilder.Append("select * from sys_part (nolock) where part_no='" + mesWmsReworks[i].ItemPartNo + "'");//零件号ID
                    List<SysPart> sysPart = dbConn.Query<SysPart>(partNoStringBuilder.ToString()).ToList();
                    if (sysPart.Count > 0)
                    {
                        parameters.Add("@partId", sysPart[0].PartId);//
                        parameters.Add("@partNo", sysPart[0].PartNo);//零件号
                        parameters.Add("@partSpec", sysPart[0].PartSpec);//简码
                        parameters.Add("@unit", sysPart[0].Unit);//单位
                    }
                    else
                    {
                        parameters.Add("@partId", 0);//零件号ID
                        parameters.Add("@partNo", mesWmsReworks[i].ItemPartNo);//零件号
                        parameters.Add("@partSpec", "");//简码
                        parameters.Add("@unit", "");//单位
                    }

                    parameters.Add("@qty", mesWmsReworks[i].ItemQty);//数量
                    parameters.Add("@pickQty", 0);//
                    parameters.Add("@outQty", 0);//
                    parameters.Add("@recQty", 0);//

                    parameters.Add("@planDate", "");//计划日期
                    parameters.Add("@planTime", "");//计划时间
                    parameters.Add("@itemStatus", "10");//状态
                    parameters.Add("@srcWarehouseId", 0);//源仓库ID
                    parameters.Add("@srcWarehouseName", "");//源仓库
                    parameters.Add("@srcZoneId", 0);//源库区ID
                    parameters.Add("@srcZoneName", "");//源库区
                    parameters.Add("@srcErpWarehouse", "");//源ERP库存地

                    if (sysPdlines.Count > 0)
                    {
                        parameters.Add("@destWarehouseId", sysPdlines[0].SrcWarehouseId);//目标仓库ID
                        parameters.Add("@destWarehouseName", sysPdlines[0].SrcWarehouseName);//目标仓库
                        parameters.Add("@destZoneId", sysPdlines[0].SrcZoneId);//目标库区ID
                        parameters.Add("@destZoneName", sysPdlines[0].SrcZoneName);//目标库区

                        StringBuilder stringBuilder = new StringBuilder();
                        stringBuilder.Append(" SELECT * from sys_zone (nolock) WHERE zone_name='" + sysPdlines[i].SrcZoneName + "'");
                        List<SysZone> sysZones = dbConn.Query<SysZone>(stringBuilder.ToString()).ToList();
                        if (sysZones.Count > 0)
                        {
                            parameters.Add("@destErpWarehouse", sysZones[0].ErpWarehouse);//目标ERP库存地
                        }
                        else
                        {
                            parameters.Add("@destErpWarehouse", "");//目标ERP库存地
                        }
                    }
                    else
                    {
                        parameters.Add("@destWarehouseId", 0);//目标仓库ID
                        parameters.Add("@destWarehouseName", "");//目标仓库
                        parameters.Add("@destZoneId", 0);//目标库区ID
                        parameters.Add("@destZoneName", "");//目标库区
                        parameters.Add("@destErpWarehouse", "");//目标ERP库存地
                    }

                    if (sysFactory.Count > 0)
                    {
                        parameters.Add("@factoryId", sysFactory[0].FactoryId);//工厂ID
                    }
                    else
                    {
                        parameters.Add("@factoryId", 0);
                    }
                    parameters.Add("@factoryCode", mesWmsReworks[i].FactoryCode);//工厂代码
                    parameters.Add("@enabled", "Y");
                    parameters.Add("@createUserid", 0);
                    parameters.Add("@createTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));//更新日期
                    parameters.Add("@guid", Guid.NewGuid().ToString());//
                    parametersList.Add(parameters);

                    SqlStringBuilder = new StringBuilder(1024);
                    SqlStringBuilder.Append("update MES_WMS_REWORK_PART set syn_flg=@synFlg,syn_time=@synTime  where pdline_code=@pdlineCode and item_part_no=@itemPartNo ");//更新同步状态
                    sqlList.Add(SqlStringBuilder.ToString());

                    parameters = new DynamicParameters();
                    parameters.Add("@pdlineCode", mesWmsReworks[i].PdlineCode);//产线
                    parameters.Add("@itemPartNo", mesWmsReworks[i].ItemPartNo);//零件号
                    parameters.Add("@serialNumber", mesWmsReworks[i].SerialNumber);//条码
                    parameters.Add("@synFlg", "20");//
                    parameters.Add("@synTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));//同步时间
                    parametersList.Add(parameters);
                }

                bool staResult = ExecuteTransaction(sqlList, parametersList);
                if (staResult)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
    }
}
